package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;





import com.dudu.bean.ParamBean;
import com.dudu.model.CallStatus;
import com.dudu.model.DialStats;

import core.CoreDao;
import core.db.DBConnectionUtil;
/**
 * 
 * @author 天使UYi
 *
 */
public class CallStatusDao {

	private static final Logger log =  core.util.Logger.getLogger(CallStatusDao.class.getSimpleName());
	
	public static void main(String[] args) {

//		CoreDao coreDao = new CoreDaoInvoke();
//		CallStatusDao callStatusDao = new CallStatusDao();
		
		CallStatus status = new CallStatus();
		
		status.setvCalleeNbr("156");
		status.setvCallerNbr("234");
		status.setvCallState("makecall");
		status.setvReason("180");
		status.setvServiceType("0");
		status.setvSessionsId("11574");
		status.setvStateTime("2015-12-23 12:23:00");
		status.setvType("0");
		
//		status.getvSessionsId();
		
//		callStatusDao.insert(coreDao, status);
//		CallStatus bean = callStatusDao.query(coreDao, status);
//		List<JSONObject> bean = callStatusDao.queryAll(coreDao, "", "");//会死的
		
		
		/*status.setvCallState("makecal5");
		status.setvSessionsId("123");
		status.setvCalleeNbr("156");
		callStatusDao.update(coreDao, status);
		*/
		
		/*status.setId(175018);
		callStatusDao.delete(coreDao, status);
		*/
	}

	/**
	 * 获取CallStatus信息的条数
	 * @param dao
	 * @param paramSql
	 * @return
	 */
	public int getTotalCall(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.getTotalCall");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}else{
			paramSql="";
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("select count(1) as totalCall from t_call_status call_status where 1=1");
			sql.append(paramSql);

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalCall");
			log.info("getTotalCall="+core.util.StringUtils.escapeLog(sql.toString()));   

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	
	/**
	 * 获取CallStatus信息的条数
	 * @param dao
	 * @param paramSql
	 * @return
	 */
	/*
	public int getTotalAcount(CoreDao dao, String paramSql) {
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" select count(1) as totalAcount "
					+ " from t_call_status "
					+ " where 1=1 "
					+ paramSql );
			
//			"where 1=1 "使得where 的条件为永真，得到的结果就是未加约束条件的

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalAcount");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	*/
	
	public int insert(CoreDao dao, CallStatus bean) {
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//执行sql拼写
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_call_status(vType, vServiceType, vSessionsId, vCallerNbr, vCalleeNbr, vCallState, vStateTime, vReason)");
			sql.append("VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						bean.getvType(),
						bean.getvServiceType(),
						bean.getvSessionsId(),
						bean.getvCallerNbr(),
						bean.getvCalleeNbr(),
						bean.getvCallState(),
						bean.getvStateTime(),
						bean.getvReason()
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 删除CallStatus
	 * @param dao
	 * @param bean
	 * @return
	 */
	public int delete(CoreDao dao, String callStatusId) {
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer(" delete from t_call_status where id =");
			sql.append("?");
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { callStatusId });

			if (list != null && list.size() > 0) {
//				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	
	public int update(CoreDao dao, CallStatus bean) {
		//数据库连接
		Connection con = null;
		int result = -1;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写SQL
			/*StringBuffer sql = new StringBuffer(
					" UPDATE t_call_status set vCallState=? ");
			sql.append(" where vSessionsId=? and vCalleeNbr=? ");*/
			
			StringBuffer sql = new StringBuffer(
					" UPDATE t_call_status set vType=?,vServiceType=?,vSessionsId=?,vCallerNbr=?,vCalleeNbr=?,vCallState=?,vStateTime=?,vReason=? ");
			sql.append(" where id=?");
			
			//执行sql，更新相应数据
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						bean.getvType(),
						bean.getvServiceType(),
						bean.getvSessionsId(),
						bean.getvCallerNbr(),
						bean.getvCalleeNbr(),
						bean.getvCallState(),
						bean.getvStateTime(),
						bean.getvReason()
					}
			);

			if (list != null && list.size() > 0) {
//				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
		
	}
	
	/**
	 * 查询所有CallStatus信息
	 * @param dao
	 * @param paramSql
	 * @param pageSql
	 * @return
	 */
	public List<CallStatus> queryAllCall(CoreDao dao, String pageSql, String paramSql) {
		log.info("CallStatusDao.queryAllCall");
		//数据库连接
		Connection con = null;
		//最后所需返回值
		List<CallStatus> result = null;
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}else{
			paramSql="";
		}

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer(
					" select call_status.* "
					+ " from t_call_status call_status "
					+ " where 1=1 "
					+ paramSql
					+ " order by call_status.vStateTime desc, call_status.vSessionsId asc, call_status.vCalleeNbr asc "
					+ pageSql );
			log.info("queryAllCall="+core.util.StringUtils.escapeLog(sql.toString()));   
			//查询数据
//			result = dao.serviceJsonSql(con, sql.toString(),new Object[] { null });
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
			//将json类型转化为CallStatus
			if (list != null) {
				List<CallStatus> items = dao.covertToBean(list, CallStatus.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	

	/**
	 * 功能：根据CallStatus的id来查询所有CallStatus信息
	 * @param dao
	 * @param bean
	 * @return
	 */
	public CallStatus query(CoreDao dao, CallStatus bean) {
		log.info("CallStatusDao.query");
		
		Connection con = null;
		CallStatus result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			/*StringBuffer sql = new StringBuffer(
					"select id,vType,vServiceType,vSessionsId,vCallerNbr," +
					"vCalleeNbr,vCallState,vStateTime,vReason " +
					"FROM t_call_status where vSessionsId=?");
			sql.append(" and vCalleeNbr = ?");*/
			
			StringBuffer sql = new StringBuffer(
					"select id,vType,vServiceType,vSessionsId,vCallerNbr," +
					"vCalleeNbr,vCallState,vStateTime,vReason " +
					"FROM t_call_status where id=?");
			
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),new Object[] { bean.getId() });
			//将json类型转化为CallStatusDao
			if (list != null && list.size() > 0) {
				List<CallStatus> items = dao.covertToBean(list, CallStatus.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	
	}
	
	
	public List<CallStatus> queryBySessionId(CoreDao dao, CallStatus bean) {
		log.info("CallStatusDao.queryBySessionId");
		Connection con = null;
		List<CallStatus> result = null;
		
		try {
			
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"select id,vType,vServiceType,vSessionsId,vCallerNbr," +
					"vCalleeNbr,vCallState,vStateTime,vReason " +
					"FROM t_call_status where vSessionsId=?");
			
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { 
				
				bean.getvSessionsId(),
				
			});
			
			if (list != null && list.size() > 0) {
				List<CallStatus> items = dao.covertToBean(list, CallStatus.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	
	}
	
	
	/**
	 * 功能：根据CallStatus的CallState来查询所有CallStatus信息
	 * @param dao
	 * @param bean
	 * @return
	 */
	public List<CallStatus> queryByCallState(CoreDao dao, CallStatus bean) {
		log.info("CallStatusDao.queryByCallState");
		Connection con = null;
		List<CallStatus> result = null;
		
		try {
			
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"select id,vType,vServiceType,vSessionsId,vCallerNbr," +
					"vCalleeNbr,vCallState,vStateTime,vReason " +
					"FROM t_call_status where vCallState=?");
			
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { 
				
				bean.getvCallState(),
				
			});
			
			if (list != null && list.size() > 0) {
				List<CallStatus> items = dao.covertToBean(list, CallStatus.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	
	}
	
	
	/**
	 * 功能：主叫通话统计，查询所有主叫呼叫次数(各个省市)
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public List<DialStats> queryCallTimes(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryCallTimes");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		List<DialStats> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT t.area, COUNT(t.area) AS call_times FROM");
			sql.append(" (SELECT DISTINCT(i.call_identifier) AS 'sessionId', IFNULL(p.area, '未知区域') AS area FROM t_meet_ims i");
			sql.append(" LEFT JOIN t_mobilephone p ON p.mobile_number=SUBSTRING(i.caller,1,7)");
			sql.append(" WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473' ");
			sql.append(paramSql);
			sql.append(" )t GROUP BY t.area;");
			log.info("queryCallTimes=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			
			if (list != null && list.size() > 0) {
				List<DialStats> items = dao.covertToBean(list, DialStats.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 功能：主叫通话统计，查询所有主叫未接听次数(各个省市)
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public List<DialStats> queryUnrecTimes(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryUnrecTimes");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		List<DialStats> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT t.area,count(1) AS unrec_times FROM");
			sql.append(" (SELECT DISTINCT(i.call_identifier) AS sessionId, i.caller AS caller,IFNULL(p.area, '未知区域') AS area,");
			sql.append(" (SELECT count(1) FROM t_meet_ims_callback c WHERE c.session_id=i.call_identifier) AS receive_people,");
			sql.append(" (SELECT count(DISTINCT(s.vCalleeNbr)) FROM t_call_status s WHERE s.vSessionsId=i.call_identifier) AS rec_state_times");
			sql.append(" FROM t_meet_ims i");
			sql.append(" LEFT JOIN t_mobilephone p ON p.mobile_number=SUBSTRING(i.caller,1,7)");
			sql.append(" WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473'");
			sql.append(paramSql);
			sql.append(" ) t WHERE t.receive_people=0 AND t.rec_state_times=1 GROUP BY t.area;");
			log.info("queryUnrecTimes=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			
			if (list != null && list.size() > 0) {
				List<DialStats> items = dao.covertToBean(list, DialStats.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 功能：按应用查询所有主叫呼叫次数
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public List<DialStats> queryCallTimesByApp(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryCallTimesByApp");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		List<DialStats> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT IFNULL(app.`name`, '未知应用') AS appname, COUNT(*) AS call_times FROM t_meet_ims i");
			sql.append(" LEFT JOIN t_app app ON i.app_id=app.id");
			sql.append(" WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473'");
			sql.append(paramSql);
			sql.append(" GROUP BY app.id;");
			log.info("queryCallTimesByApp=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			
			if (list != null && list.size() > 0) {
				List<DialStats> items = dao.covertToBean(list, DialStats.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 功能：按应用查询所有主叫未接听次数
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public List<DialStats> queryUnrecTimesByApp(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryUnrecTimesByApp");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		List<DialStats> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT IFNULL(app.`name`, '未知应用') AS appname,count(1) AS unrec_times FROM t_app app,");
			sql.append(" (SELECT DISTINCT(i.call_identifier) AS sessionId, i.app_id,");
			sql.append(" (SELECT count(1) FROM t_meet_ims_callback c WHERE c.session_id=i.call_identifier) AS receive_people,");
			sql.append(" (SELECT count(DISTINCT(s.vCalleeNbr)) FROM t_call_status s WHERE s.vSessionsId=i.call_identifier) AS rec_state_times FROM t_meet_ims i");
			sql.append(" WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473'");
			sql.append(paramSql);
			sql.append(" ) t WHERE t.receive_people=0 AND t.rec_state_times=1 AND app.id=t.app_id GROUP BY t.app_id;");
			log.info("queryUnrecTimesByApp=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			
			if (list != null && list.size() > 0) {
				List<DialStats> items = dao.covertToBean(list, DialStats.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 功能：回呼号码统计，呼叫次数
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public List<DialStats> queryCallBackTimes(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryCallBackTimes");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		List<DialStats> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT i.chargeNumber AS charge_number, COUNT(i.chargeNumber) AS call_times");
			sql.append(" FROM t_meet_ims i WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473' ");
			sql.append(paramSql);
			sql.append(" GROUP BY i.chargeNumber;");
			log.info("queryCallBackTimes=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			
			if (list != null && list.size() > 0) {
				List<DialStats> items = dao.covertToBean(list, DialStats.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 功能：回呼号码统计，未接听次数
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public List<DialStats> queryCallBackUnrecTimes(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryCallBackUnrecTimes");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		List<DialStats> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT m.vCallerNbr AS charge_number,count(m.vCallerNbr) AS unrec_times FROM(");
			sql.append(" SELECT DISTINCT(s.vSessionsId), s.vCallerNbr FROM t_call_status s,");
			sql.append(" (SELECT DISTINCT(i.call_identifier) AS sessionId,");
			sql.append(" (SELECT count(1) FROM t_meet_ims_callback c WHERE c.session_id=i.call_identifier) AS receive_people,");
			sql.append(" (SELECT count(DISTINCT(s.vCalleeNbr)) FROM t_call_status s WHERE s.vSessionsId=i.call_identifier) AS rec_state_times");
			sql.append(" FROM t_meet_ims i WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473' ");
			sql.append(paramSql);
			sql.append(" ) t WHERE s.vSessionsId=t.sessionId AND t.receive_people=0 AND t.rec_state_times=1");
			sql.append(" ) m GROUP BY m.vCallerNbr;");
			log.info("queryCallBackUnrecTimes=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			
			if (list != null && list.size() > 0) {
				List<DialStats> items = dao.covertToBean(list, DialStats.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 功能：主叫未接听原因统计，统计失败次数
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public int queryAllUnrecReasons(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryAllUnrecReasons");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		int result = -1;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT COUNT(pp.sessionId) AS unrec_times FROM (");
			sql.append(" SELECT t.sessionId,s.vCallState,s.vReason FROM t_call_status s,");
			sql.append(" (SELECT DISTINCT(i.call_identifier) AS sessionId,");
			sql.append(" (SELECT count(1) FROM t_meet_ims_callback c WHERE c.session_id=i.call_identifier) AS receive_people,");
			sql.append(" (SELECT count(DISTINCT(s.vCalleeNbr)) FROM t_call_status s WHERE s.vSessionsId=i.call_identifier) AS rec_state_times");
			sql.append(" FROM t_meet_ims i");
			sql.append(" WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473' ");
			sql.append(paramSql);
			sql.append(" ) t WHERE s.vSessionsId=t.sessionId AND t.receive_people=0 AND t.rec_state_times=1 AND (s.vCallState='FAILED' OR s.vCallState='CANCEL')");
			sql.append(" GROUP BY t.sessionId) pp;");
			log.info("queryAllUnrecReasons=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("unrec_times");
//			log.info("unrec_times="+core.util.StringUtils.escapeLog(sql.toString()));  

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 功能：主叫未接听原因统计，按照各原因统计次数
	 * @param dao
	 * @param paramSql sql串
	 * @return
	 */
	public List<DialStats> queryUnrecReasons(CoreDao dao, String paramSql) {
		log.info("CallStatusDao.queryUnrecReasons");
		// 拼接sql语句
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		List<DialStats> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer("SELECT pp.vReason AS reason,COUNT(pp.vReason) AS unrec_times FROM (");
			sql.append(" SELECT t.sessionId,s.vCallState,s.vReason FROM t_call_status s,");
			sql.append(" (SELECT DISTINCT(i.call_identifier) AS sessionId,");
			sql.append(" (SELECT count(1) FROM t_meet_ims_callback c WHERE c.session_id=i.call_identifier) AS receive_people,");
			sql.append(" (SELECT count(DISTINCT(s.vCalleeNbr)) FROM t_call_status s WHERE s.vSessionsId=i.call_identifier) AS rec_state_times");
			sql.append(" FROM t_meet_ims i");
			sql.append(" WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473' ");
			sql.append(paramSql);
			sql.append(" ) t WHERE s.vSessionsId=t.sessionId AND t.receive_people=0 AND t.rec_state_times=1 AND (s.vCallState='FAILED' OR s.vCallState='CANCEL')");
			sql.append(" GROUP BY t.sessionId) pp GROUP BY pp.vReason ORDER BY unrec_times DESC;");
			log.info("queryUnrecReasons=" + sql.toString());
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			
			if (list != null && list.size() > 0) {
				List<DialStats> items = dao.covertToBean(list, DialStats.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	
	
	/**
	 * 查询所有省份的名称
	 * @param dao
	 * @return
	 */
	public List<ParamBean> queryAllAreas(CoreDao dao){
		log.info("CallStatusDao.queryAllAreas");
		//数据库连接
		Connection con = null;
		List<ParamBean> result = null;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT area.SHORT_NAME AS text,area.SHORT_NAME AS id");
			sql.append(" FROM t_area_code area WHERE area.PARENT_REGION='0' GROUP BY area.REGION_CODE");
			log.info("queryAllAreas="+sql);
			
			//查询相应信息
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			if (list != null && list.size() > 0) {
				List<ParamBean> items = dao.covertToBean(list, ParamBean.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 查询所有回呼号码
	 * @param dao
	 * @return
	 */
	public List<ParamBean> queryAllCallback(CoreDao dao){
		log.info("CallStatusDao.queryAllCallback");
		//数据库连接
		Connection con = null;
		List<ParamBean> result = null;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT i.chargeNumber AS text,i.chargeNumber AS id");
			sql.append(" FROM t_meet_ims i ");
			sql.append(" WHERE i.ability_type = 'TalksV2' AND i.caller != '17001097473'");
			sql.append(" GROUP BY i.chargeNumber;");
			log.info("queryAllCallback="+sql);
			
			//查询相应信息
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
			if (list != null && list.size() > 0) {
				List<ParamBean> items = dao.covertToBean(list, ParamBean.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	
}
